package com.server.dal;

import com.server.db.DBUtils;
import com.server.db.SQLHelper;

import java.security.spec.RSAOtherPrimeInfo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author e4glet
 * 2025年3月5日
 * */

// 数据库操作类
public class DatabaseHelper {
    

    // 用户注册方法
    public static boolean registerUser(String email, String nickname, String password) throws SQLException {
        System.out.println("注册邮箱："+email);
        // 先检查邮箱是否已存在
        String checkQuery = "SELECT COUNT(*) FROM user_tb WHERE Email = ?";
        // 新建参数列表
        Object[] param = new Object[]{email};
        try {
            Integer count = SQLHelper.ExecuteUpdate(checkQuery,param);
            if (count > 0) {
                return false; // 邮箱已存在
            }
        }catch (SQLException e){
            System.out.println(e);
        }

        //查询当前用户表中最大的QQ号是多少，并以此为参考依据给新用户添加新QQ号
        int newQQNumber = 0;
        String getMaxQQNumberString = "select MAX(qq) from user_tb";
        try {
            ResultSet rs = SQLHelper.ExecuteReader(getMaxQQNumberString,null);
            if (rs.next()) {
                newQQNumber = rs.getInt(1) + 1;
            }
        }catch (SQLException e){
            System.out.println(e);
        }

        // 执行注册
        String insertQuery = "INSERT INTO user_tb (QQ,Email, Nickname, Password, online) VALUES (?, ?, ?, ?, false)";
        // 添加注册参数列表对象
        Object[] regUserlist = new Object[]{newQQNumber,email,nickname,password};
        try {
            Integer count = SQLHelper.ExecuteUpdate(insertQuery,regUserlist);
            if (count>0){
                return true;
            }
        }catch (SQLException e){
            System.out.println(e);
        }

        return false;
    }

    // 用户登录方法
    public static boolean loginUser(String account, String password) throws SQLException {
        // 登录验证sql语句
        String query = "SELECT * FROM user_tb WHERE (Email = ? OR QQ = ?) AND Password = ?";
        // 参数列表
        int qq = -1;
        try {
            qq = Integer.parseInt(account);
        } catch (NumberFormatException e) {
            qq = -1;
        }
        Object[] param = new Object[]{account,qq,password};

        try (ResultSet rs = SQLHelper.ExecuteReader(query,param)) {
            return rs.next();
        }catch (Exception e){
            System.out.println(e);
        }

        return false;
    }

    // 根据邮箱或者qq查询用户id
    public static int getUserId(String account) throws SQLException {
        // 查询用户sql语句
        String query = "SELECT id FROM user_tb WHERE Email = ? OR QQ = ?";
        // 参数列表
        int qq = -1;
        try {
            qq = Integer.parseInt(account);
        } catch (NumberFormatException e) {
            qq = -1;
        }
        Object[] param = new Object[]{account,qq};

        try (ResultSet rs = SQLHelper.ExecuteReader(query,param)) {
            if (rs.next()) {
                return rs.getInt("id");
            }
            return -1;
        }
    }

    // 修改添加好友方法
    public static boolean addFriend(int userId, int friendId) throws SQLException {
        //不能添加自己为好友
        if(userId == friendId){
            return false;
        }
        
        Connection conn = null;
        try {
            conn = DBUtils.Open();
            conn.setAutoCommit(false);  // 开启事务
            
            // 添加双向好友关系
            String query = "INSERT INTO friend_tb (user_id, friend_id, status) VALUES (?, ?, 'accepted')";

            try (PreparedStatement stmt = conn.prepareStatement(query)) {
                // 为用户A添加好友B
                stmt.setInt(1, userId);
                stmt.setInt(2, friendId);
                stmt.executeUpdate();
                
                // 为用户B添加好友A
                stmt.setInt(1, friendId);
                stmt.setInt(2, userId);
                stmt.executeUpdate();
                
                conn.commit();
                return true;
            }
        } catch (SQLException e) {
            if (conn != null) {
                conn.rollback();
            }
            throw e;
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
        }
    }

    // 查询好友是否存在于好友关系表中
    public static boolean checkFriendExists(int userId, int friendId) throws SQLException {
        // 查询好友关系SQL语句
        String query = "SELECT * FROM friend_tb WHERE (user_id = ? AND friend_id = ?) OR (user_id = ? AND friend_id = ?)";
        // sql语句参数列表
        Object[] param = new Object[]{userId ,friendId,friendId,userId};

        try (ResultSet rs = SQLHelper.ExecuteReader(query,param)) {
            return rs.next();
        }catch (SQLException e){
            System.out.println(e);
        }

        return false;
    }

    /**
     * 查询用户通过邮箱进行查询
     * @account 用户邮箱或QQ
     * */
    public static Map<String, Object> searchUserByAccount(String account) throws SQLException {
        // 执行查询语句
        String query = "SELECT id, Nickname FROM user_tb WHERE Email = ? OR QQ = ?";
        // 参数列表
        int qq = -1;
        try {
            qq = Integer.parseInt(account);
        } catch (NumberFormatException e) {
            qq = -1;
        }
        Object[] param = new Object[]{account,qq};

        Map<String, Object> result = new HashMap<>();

        try (ResultSet rs = SQLHelper.ExecuteReader(query,param)) {
            if (rs.next()) {
                result.put("id", rs.getInt("id"));
                result.put("nickname", rs.getString("Nickname"));
                return result;
            }
        }catch (SQLException e){
            System.out.println(e);
        }

        return null;
    }

    // 保存用户消息
    public static boolean saveMessage(int senderId, int receiverId, String message) throws SQLException {
        // 添加用户消息
        String query = "INSERT INTO message_tb (sender_id, receiver_id, message) VALUES (?, ?, ?)";
        // sql语句占位符参数列表
        Object[] param = new Object[]{senderId,receiverId,message};

        try{
            return SQLHelper.ExecuteUpdate(query,param) > 0;
        }catch (SQLException e){
            System.out.println(e);
        }

        return false;
    }

    // 通过用户id获取未读消息
    public static ResultSet getMessages(int userId) throws SQLException {
        // sql语句
        String query = "SELECT * FROM message_tb WHERE receiver_id = ? AND is_read = false";
        // sql语句占位符参数列表
        Object[] param = new Object[]{userId};

        try {
            return SQLHelper.ExecuteReader(query,param);
        }catch (SQLException e){
            System.out.println(e);
        }

        return null;
    }

    // 更新当前用户的在线状态
    public static void updateUserOnlineStatus(int userId, boolean online) throws SQLException {
        // sql语句
        String query = "UPDATE user_tb SET online = ? WHERE id = ?";
        // 参数列表
        Object[] param = new Object[]{online,userId};

        try{
            SQLHelper.ExecuteUpdate(query,param);
        }catch (SQLException e){
            System.out.println(e);
        }
    }

    /**
     * 获得当前用户的好友列表
     * @userId 当前用户id
     * */
    public static List<Map<String, Object>> getFriendList(int userId) throws SQLException {
        // sql语句
        String query = "SELECT DISTINCT u.id, u.Nickname, u.online " +
                      "FROM user_tb u " +
                      "INNER JOIN friend_tb f ON (u.id = f.friend_id OR u.id = f.user_id) " +
                      "WHERE (f.user_id = ? OR f.friend_id = ?) " +
                      "AND f.status = 'accepted' " +
                      "AND u.id != ?";  // 排除自己
        
        List<Map<String, Object>> friends = new ArrayList<>();
        // 参数列表
        Object[] param = new Object[]{userId,userId,userId};

        try (ResultSet rs = SQLHelper.ExecuteReader(query,param)) {
            while (rs.next()) {
                Map<String, Object> friend = new HashMap<>();
                friend.put("id", rs.getInt("id"));
                friend.put("nickname", rs.getString("Nickname"));
                friend.put("online", rs.getBoolean("online"));
                friends.add(friend);
            }
        }catch (SQLException e){
            System.out.println(e);
        }

        return friends;
    }

    // 更新所有用户的在线状态为离线
    public static void updateAllUsersOffline() throws SQLException {
        // sql语句
        String query = "UPDATE user_tb SET online = false";

        try{
            SQLHelper.ExecuteUpdate(query,null);
        }catch (SQLException e){
            System.out.println(e);
        }
    }

    /**
     * 根据当前用户id查询其好友列表
     * @retrun List<Integer>
     * */
    public static List<Integer> getUserListByUserId(Integer userId){
        //sql语句
        String query = "SELECT friend_id from friend_tb WHERE user_id = ? and status = 'accepted'";

        Object[] param = new Object[]{userId};

        List<Integer> onlineUserList = new ArrayList<>();
        try {
            ResultSet rs = SQLHelper.ExecuteReader(query,param);
            while (rs.next()){
                onlineUserList.add(rs.getInt("friend_id"));
            }
            return  onlineUserList;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
}